package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Util.JdbcUtil;
import Entity.*;

public class OpreateNotesDao {
	//显示操作记录
	public List<OpreateNotesEntity> select(){
		List<OpreateNotesEntity>list = new ArrayList<OpreateNotesEntity>();
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from ("
					+ "select num,project,name,pay_total,operator,time,nullify,code from pay "
					+ "UNION ALL "
					+ "select num,project,name,refund_total,operator,time,nullify,code from refund"
					+ ")t "
					+ "where nullify=0 ORDER BY time DESC;";
			//ASC
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				OpreateNotesEntity notes = new OpreateNotesEntity();
				notes.setCode(rs.getInt(1));
				notes.setOpreateproject(rs.getInt(2));
				notes.setName(rs.getString(3));
				notes.setPrice(rs.getDouble(4));
				notes.setOperator(rs.getString(5));
				notes.setTime(rs.getString(6));
				notes.setNum(rs.getInt(8));
				list.add(notes);
			}
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	//近期操作记录
	public List<OpreateNotesEntity> select_pages(){
		List<OpreateNotesEntity>list = new ArrayList<OpreateNotesEntity>();
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from ("
					+ "select num,project,name,pay_total,operator,time,nullify,code from pay "
					+ "UNION ALL "
					+ "select num,project,name,refund_total,operator,time,nullify,code from refund"
					+ ")t "
					+ "where nullify=0 ORDER BY time DESC limit 0,30;";
			//ASC
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				OpreateNotesEntity notes = new OpreateNotesEntity();
				notes.setCode(rs.getInt(1));
				notes.setOpreateproject(rs.getInt(2));
				notes.setName(rs.getString(3));
				notes.setPrice(rs.getDouble(4));
				notes.setOperator(rs.getString(5));
				notes.setTime(rs.getString(6));
				notes.setNum(rs.getInt(8));
				list.add(notes);
			}
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
}
